﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;
using System.Text;
using Oracle.DataAccess.Client;
using System.Data;

namespace Wicresoft.UnifyShow.Business
{
    public static class ToDoHelper
    {

        private static readonly string itsmCM = @"(SELECT Entry_ID as RequestID, Incident_Number AS IDS,'事件' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,case ToDo.cchr_submitterfullname when '自服务门户' THEN '自服务系统' when '监控系统' THEN '监控系统' ELSE ToDo.cchr_submitterfullname END as  Submitter,ToDo.Description,'' AS BDSummary,'指派' as Operation,  
CASE ToDo.Status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '处理中' WHEN 3 THEN '暂停' ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.Assignee AS Assigner, ToDo.Assigned_Group AS AssignGroup FROM aradmin.hpd_help_desk ToDo
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE  ((( ToDo.Assignee is null AND EXISTS(SELECT csga.support_group_id FROM  aradmin.ctm_support_group_association csga WHERE 
ToDo.Assigned_Group_ID=csga.support_group_id AND csga.login_id=:loginID))) OR ToDo.assignee_login_id=:loginID)
 AND ToDo.status<4 AND ToDo.status>0)
Union ALL 
(SELECT sys_problem_investigation_id as RequestID,  Problem_Investigation_ID AS IDS,'问题' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.Description,'' AS BDSummary,'指派' as Operation,  
CASE ToDo.Investigation_Status  WHEN 0 THEN '草稿'  WHEN 1 THEN '评审中' WHEN 2 THEN '授权请求' WHEN 3 THEN '已指派' WHEN 4 THEN '调查中' WHEN 5 THEN '暂停' ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.Assignee AS Assigner, ToDo.Assigned_Group AS AssignGroup FROM aradmin.pbm_problem_investigation ToDo 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE  ((( ToDo.Assignee is null AND EXISTS(SELECT csga.support_group_id FROM  aradmin.ctm_support_group_association csga WHERE 
ToDo.Assigned_Group_ID=csga.support_group_id AND csga.login_id=:loginID))) OR ToDo.Assignee_Login_ID=:loginID OR ToDo.Problem_Manager_Login=:loginID)
AND ToDo.Investigation_Status<6 and ToDo.Investigation_Status>0)
Union ALL 
(SELECT  ToDo.Task_ID AS RequestID, ToDo.Task_ID AS IDS,'任务' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Create_Date+28800,'second')as CreateDate,
cp.full_name as  Submitter,ToDo.Summary AS description,'' AS BDSummary,'指派' as Operation,  
CASE ToDo.Status WHEN 2000 THEN '已指派' ELSE '其他' END  AS Status,
CASE ToDo.Priority WHEN 0 THEN '严重'  WHEN 1 THEN '高' WHEN 2 THEN '中等' WHEN 3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.Assignee AS Assigner, ToDo.Assignee_Group AS AssignGroup FROM aradmin.tms_task ToDo 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE  ToDo.Assigned_To=:loginID AND ToDo.Status=2000)
Union ALL
(SELECT ToDo.Request_ID AS RequestID, ToDo.infrastructure_change_id as IDS, '变更' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.Description,'' AS BDSummary,'审批'as Operation,
CASE  ToDo.Change_Request_Status  WHEN 0 THEN '已提交' WHEN 1 THEN '已设计'  WHEN 2 THEN '变更请求'  ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN  3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.ASCHG AS Assigner,ToDo.ASGRP AS AssignGroup FROM aradmin.ap_detail_signature ads
INNER JOIN aradmin.chg_infrastructure_change ToDo ON ads.request=ToDo.request_id
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE ads.approval_status=0 AND ads.application='CHG:Infrastructure Change' AND ads.approvers LIKE '%' || :loginID || '%' )
Union ALL
(SELECT ToDo.Request_ID AS RequestID, ToDo.infrastructure_change_id as IDS,'变更' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.Submit_Date+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.Description,'' AS BDSummary,'指派'as Operation,
CASE  ToDo.Change_Request_Status WHEN 0 THEN '已提交'  WHEN 1 THEN '已设计'  WHEN 2 THEN '变更请求' WHEN 3 THEN '进行中的计划' WHEN 7 THEN '正在执行' 
WHEN 8 THEN '暂停' ELSE '其他' END  AS Status,
CASE ToDo.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN  3 THEN '低' ELSE '其他' END  AS Priority,
ToDo.ASCHG AS Assigner,ToDo.ASGRP AS AssignGroup FROM aradmin.chg_infrastructure_change ToDo 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
WHERE ToDo.ASLOGID=:loginID AND ToDo.Change_Request_Status<9 AND ToDo.Change_Request_Status!=1 AND ToDo.Change_Request_Status!=2 )
Union ALL
(SELECT ToDo.Request_ID AS RequestID, ToDo.DOCID as IDS, '知识' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(ToDo.CREATE_DATE+28800,'second')
as CreateDate,cp.full_name as  Submitter,ToDo.ARTICLETITLE as Description,'' AS BDSummary,'审批'as Operation,
'发布审批'  AS Status,
''  AS Priority,
cpa.FULL_NAME AS Assigner,'文章审批人' AS AssignGroup  FROM aradmin.ap_detail_signature ads
INNER JOIN ARADMIN.RKM_KNOWLEDGEARTICLEMANAGER ToDo ON ads.request=ToDo.request_id
LEFT OUTER JOIN aradmin.ctm_people cp ON cp.remedy_login_id=ToDo.submitter 
LEFT OUTER JOIN aradmin.ctm_people cpa ON cpa.remedy_login_id=:loginID 
WHERE ads.approval_status=0 AND ads.application='RKM:KnowledgeArticleManager' AND ads.approvers LIKE '%' || :loginID || '%')
";
        private static readonly string sfCM = @"(SELECT ToDo.ServiceRequestID AS RequestID, 'SFR0'|| ToDo.ServiceRequestID AS IDS, '自服务' as TypeS,ToDo.CreateTime as CreateDate,
d.UserName AS Submitter,ToDo.description AS Description,ToDo.BDSummary AS BDSummary,'审批' as Operation,  
CASE ToDo.Stauts  WHEN 0 THEN '草稿'  WHEN 1 THEN '审批中' WHEN 2 THEN '' WHEN 3 THEN '处理中' ELSE '其他' END  AS Status,
'' as Priority, '' AS Assigner,
CASE  ToDo.step WHEN 1 THEN '审批人' WHEN 2 THEN '主任' WHEN 3 THEN '审批人'  WHEN 4 THEN '主任'  ELSE '其他' END  
AS AssignGroup FROM ADMIN.servicerecords ToDo
INNER JOIN admin.accountcopy a  ON ToDo.companyids like  ('%'||a.company||'%')
INNER JOIN admin.userrolerelationship b ON a.accountid=b.accountid
INNER JOIN admin.approvalrole c ON  b.roleid=c.ID and c.id=ToDo.step
INNER JOIN admin.accountcopy d  ON ToDo.accountid= d.accountid
WHERE ToDo.Stauts=1 and a.accountid=:loginID )";

        private static readonly string unifyshowCM = @"(select  t.ID||'' AS RequestID,t.JOBNO AS IDS,'任务'  AS TypeS, t.CREATETIME as CreateDate,u1.DisplayName AS Submitter, t.TaskName  as Description, '' AS BDSummary, '执行'as Operation, '进行中' as Status,
'' as Priority,u.DisplayName AS Assigner,'' as AssignGroup
from PADMIN.TASK  t 
INNER join PADMIN.USER_X u on u.ID=t.OWNERID
INNER join PADMIN.USER_X u1 on u1.ID=t.CreateUserID
where t.STATUS=1 AND u.NAME=:loginID)";

        private static readonly string itsmCMDo = @"(select distinct Entry_ID as RequestID, Incident_Number AS IDS,'事件' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Do.Submit_Date+28800,'second') as CreateDate,case Do.cchr_submitterfullname when '自服务门户' THEN '自服务系统' when '监控系统' THEN '监控系统' ELSE Do.cchr_submitterfullname END as  Submitter,Do.Description,'' AS BDSummary,'指派' as Operation, CASE Do.Status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '处理中' WHEN 3 THEN '暂停' WHEN 4 THEN '已解决' WHEN 5 THEN '已关闭' WHEN 6 THEN '已取消' ELSE '其他' END  AS Status,CASE Do.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority, Do.Assignee AS Assigner, Do.Assigned_Group AS AssignGroup from hpd_help_desk do 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=Do.submitter 
where do.submitter=:loginID and do.Status< 4 and do.Status>0 and (do.assignee_login_id!=:loginID or do.assignee_login_id is null)) 
UNION ALL 
(select distinct Entry_ID as RequestID, Incident_Number AS IDS,'事件' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Do.Submit_Date+28800,'second') as CreateDate,case Do.cchr_submitterfullname when '自服务门户' THEN '自服务系统' when '监控系统' THEN '监控系统' ELSE Do.cchr_submitterfullname END as  Submitter,Do.Description,'' AS BDSummary,'指派' as Operation, CASE Do.Status  WHEN 0 THEN '新建'  WHEN 1 THEN '已指派' WHEN 2 THEN '处理中' WHEN 3 THEN '暂停' WHEN 4 THEN '已解决' WHEN 5 THEN '已关闭' WHEN 6 THEN '已取消' ELSE '其他' END  AS Status,CASE Do.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN 3 THEN '低' ELSE '其他' END  AS Priority, Do.Assignee AS Assigner, Do.Assigned_Group AS AssignGroup from hpd_help_desk do LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=Do.submitter where (do.assignee_login_id!=:loginID or do.assignee_login_id is null) and do.Status< 4 and do.Status>0 and do.incident_number in (select au.incident_number from hpd_help_desk_auditform au where au.assignee_login_id=:loginID))
Union ALL 
(SELECT distinct Do.Request_ID AS RequestID, Do.infrastructure_change_id AS IDS,'变更' as TypeS,to_date('1970-1-1','yyyy-mm-dd')+numtodsinterval(Do.Submit_Date+28800,'second') as CreateDate,cp.full_name as Submitter,Do.Description,'' AS BDSummary,'审批'as Operation,CASE  Do.Change_Request_Status  WHEN 0 THEN '已提交'  WHEN 1 THEN '已设计'  WHEN 2 THEN '变更请求' WHEN 3 THEN '进行中的计划' WHEN 7 THEN '正在执行' WHEN 8 THEN '暂停' WHEN 9 THEN '已拒绝' WHEN 10 THEN '已完成' WHEN 11 THEN '已关闭' WHEN 12 THEN '已取消' ELSE '其他' END  AS Status,CASE Do.Priority   WHEN 1 THEN '高' WHEN 2 THEN '中' WHEN  3 THEN '低' ELSE '其他' END  AS Priority,Do.ASCHG AS Assigner,Do.ASGRP AS AssignGroup from chg_infrastructure_change Do 
LEFT OUTER JOIN ctm_people cp ON cp.remedy_login_id=Do.submitter 
where Do.Change_Request_Status<9 and ((Do.submitter=:loginID and do.aslogid!=:loginID) or (Do.aslogid=:loginID and (Do.Change_Request_Status=1 or Do.Change_Request_Status=2)) or (Do.infrastructure_change_id in (select t.rootrequestid from tms_Task t where t.status=6000 and t.assigned_to=:loginID and t.rootrequestid=Do.infrastructure_change_id) and do.aslogid!=:loginID) or (do.aslogid!=:loginID and Do.request_id in (select ap.request from ap_detail_signature ap where ap.cint_isnew=1 and (ap.approvers like  '%'||:loginID|| ';%' or ap.approvers like  '%;'||:loginID||'%' or ap.approvers =:loginID) and (ap.approval_status=1 or ap.approval_status=2 or ap.ddl_ApprovalStatus=8)))))";
        private static readonly string sfCMDo = @"select Do.ServiceRequestID AS RequestID, 'SFR0'|| Do.ServiceRequestID AS IDS, '自服务' as TypeS, do.createtime as CreateDate,a.UserName AS Submitter, Do.description AS Description,Do.BDSummary AS BDSummary,'审批' as Operation, CASE Do.Stauts WHEN 1 THEN '审批中' WHEN 2 THEN '等待中' WHEN 3 THEN '处理中' WHEN 4 THEN '已完成' WHEN 5 THEN '已拒绝' WHEN 6 THEN '已取消' ELSE '其他' END  AS Status,'' as Priority, '' AS Assigner,CASE  Do.step WHEN 1 THEN '审批人' WHEN 2 THEN '主任' WHEN 3 THEN '审批人'  WHEN 4 THEN '主任'  ELSE '其他' END  AS AssignGroup  from servicerecords Do inner join accountcopy a on a.accountid=do.accountid where Do.stauts>=1 and Do.stauts< 4 and Do.serviceRequestID in (select serviceRequestID from ServiceRecordsHistory where accountid = :loginID)";

        public static List<ToDoModel> GetListToDo(string loginID, out int Count, int? pageIndex = null, int? pageSize = null, string sortitem = null, string sortorder = null)
        {
            string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
            string connectionStrITSM = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
            string connectionUS = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;

            List<ToDoModel> result1 = new List<ToDoModel>();
            using (OracleConnection conSF = new OracleConnection(connectionStrSF), conITSM = new OracleConnection(connectionStrITSM), conUS = new OracleConnection(connectionUS))
            {
                conSF.Open();
                conITSM.Open();
                conUS.Open();
                OracleCommand commandSF, commandITSM, commandUS;
                commandSF = conSF.CreateCommand();
                commandITSM = conITSM.CreateCommand();
                commandUS = conUS.CreateCommand();
                commandSF.CommandType = System.Data.CommandType.Text;
                commandITSM.CommandType = System.Data.CommandType.Text;
                commandUS.CommandType = System.Data.CommandType.Text;
                commandSF.BindByName = true;
                commandITSM.BindByName = true;
                commandUS.BindByName = true;
                commandSF.CommandText = sfCM;
                commandITSM.CommandText = itsmCM;
                commandUS.CommandText = unifyshowCM;
                OracleParameter loginIDSF = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, loginID, System.Data.ParameterDirection.Input);
                OracleParameter loginIDITSM = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, ConvertString(loginID), System.Data.ParameterDirection.Input);
                OracleParameter loginIDUS = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, loginID, System.Data.ParameterDirection.Input);
                commandSF.Parameters.Add(loginIDSF);
                commandITSM.Parameters.Add(loginIDITSM);
                commandUS.Parameters.Add(loginIDUS);

                var result = GetListToDoFromCommand(commandSF).Concat(GetListToDoFromCommand(commandITSM)).Concat(GetListToDoFromCommand(commandUS));

                if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "asc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderBy(c);
                }
                else if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "desc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderByDescending(c);
                }
                else
                {
                    result = result.OrderByDescending(o => o.CreateDate);
                }

                Count = result.Count();

                if (pageIndex.HasValue)
                {
                    result1 = result.Skip(pageSize.Value * (pageIndex.Value - 1)).Take(pageSize.Value).ToList();
                }
                else
                {
                    result1 = result.ToList();
                }
            }
            return result1;
        }

        public static LambdaExpression GenerateSelector<TEntity>(String propertyName) where TEntity : class
        {
            // Create a parameter to pass into the Lambda expression (Entity => Entity.OrderByField).
            var parameter = Expression.Parameter(typeof(TEntity), "Entity");
            //  create the selector part, but support child properties
            PropertyInfo property;
            Expression propertyAccess;
            if (propertyName.Contains('.'))
            {
                // support to be sorted on child fields.
                String[] childProperties = propertyName.Split('.');
                property = typeof(TEntity).GetProperty(childProperties[0]);
                propertyAccess = Expression.MakeMemberAccess(parameter, property);
                for (int i = 1; i < childProperties.Length; i++)
                {
                    property = property.PropertyType.GetProperty(childProperties[i]);
                    propertyAccess = Expression.MakeMemberAccess(propertyAccess, property);
                }
            }
            else
            {
                property = typeof(TEntity).GetProperty(propertyName);
                propertyAccess = Expression.MakeMemberAccess(parameter, property);
            }
            //resultType = property.PropertyType;
            // Create the order by expression.
            return Expression.Lambda(propertyAccess, parameter);
        }

        public static List<ToDoModel> GetListDoing(string loginID, out int Count, int? pageIndex = null, int? pageSize = null, string sortitem = null, string sortorder = null)
        {
            string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
            string connectionStrITSM = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
            List<ToDoModel> result1 = new List<ToDoModel>();
            using (OracleConnection conSF = new OracleConnection(connectionStrSF), conITSM = new OracleConnection(connectionStrITSM))
            {
                conSF.Open();
                conITSM.Open();
                OracleCommand commandSF, commandITSM;
                commandSF = conSF.CreateCommand();
                commandITSM = conITSM.CreateCommand();
                commandSF.CommandType = System.Data.CommandType.Text;
                commandITSM.CommandType = System.Data.CommandType.Text;
                commandSF.BindByName = true;
                commandITSM.BindByName = true;
                commandSF.CommandText = sfCMDo;
                commandITSM.CommandText = itsmCMDo;
                OracleParameter loginIDSF = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, loginID, System.Data.ParameterDirection.Input);
                OracleParameter loginIDITSM = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, ConvertString(loginID), System.Data.ParameterDirection.Input);
                commandSF.Parameters.Add(loginIDSF);
                commandITSM.Parameters.Add(loginIDITSM);
                var result = GetListToDoFromCommand(commandSF).Concat(GetListToDoFromCommand(commandITSM));

                if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "asc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderBy(c);
                }
                else if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "desc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderByDescending(c);
                }
                else
                {
                    result = result.OrderByDescending(o => o.CreateDate);
                }
                Count = result.Count();

                if (pageIndex.HasValue)
                {
                    result1 = result.Skip(pageSize.Value * (pageIndex.Value - 1)).Take(pageSize.Value).ToList();
                }
                else
                {
                    result1 = result.ToList();
                }
            }
            return result1;
        }

        public static List<ToDoModel> GetSearchList(string connectionStr, string queryStr, out int Count, string sortitem = null, string sortorder = null)
        {
            List<ToDoModel> result1 = new List<ToDoModel>();
            using (OracleConnection con = new OracleConnection(connectionStr))
            {
                con.Open();
                OracleCommand command;
                command = con.CreateCommand();
                command.CommandType = System.Data.CommandType.Text;
                command.BindByName = true;
                command.CommandText = queryStr;

                IEnumerable<ToDoModel> result = GetListToDoFromCommand(command);

                if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "asc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderBy(c);
                }
                else if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "desc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderByDescending(c);
                }
                else
                {
                    result = result.OrderByDescending(o => o.CreateDate);
                }

                Count = result.Count();
                result1 = result.ToList();
            }
            return result1;
        }

        public static List<ToDoModel> GetSearchListAll(string queryStrITSM, string queryStrSF, out int Count, string sortitem = null, string sortorder = null)
        {
            string connectionStrSF = ConfigurationManager.ConnectionStrings["SelfServiceContext"].ConnectionString;
            string connectionStrITSM = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
            string connectionStrUnify = ConfigurationManager.ConnectionStrings["DataContext"].ConnectionString;
            List<ToDoModel> result1 = new List<ToDoModel>();
            using (OracleConnection conSF = new OracleConnection(connectionStrSF), conITSM = new OracleConnection(connectionStrITSM))
            {
                conSF.Open();
                conITSM.Open();
                OracleCommand commandSF, commandITSM;
                commandSF = conSF.CreateCommand();
                commandITSM = conITSM.CreateCommand();
                commandSF.CommandType = System.Data.CommandType.Text;
                commandITSM.CommandType = System.Data.CommandType.Text;
                commandSF.BindByName = true;
                commandITSM.BindByName = true;
                commandSF.CommandText = queryStrSF;
                commandITSM.CommandText = queryStrITSM;

              IEnumerable<ToDoModel>  result = GetListToDoFromCommand(commandSF).Concat(GetListToDoFromCommand(commandITSM)).OrderBy(o => o.IDS).ToList();
                if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "asc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderBy(c);
                }
                else if (!string.IsNullOrEmpty(sortitem) && sortorder.ToLower() == "desc")
                {
                    var c = ((Expression<Func<ToDoModel, string>>)GenerateSelector<ToDoModel>(sortitem)).Compile();
                    result = result.OrderByDescending(c);
                }
                else
                {
                    result = result.OrderByDescending(o => o.CreateDate);
                }
                Count = result.Count();
                result1 = result.ToList();
            }
            return result1;
        }

        private static string ConvertString(string str)
        {
            if (str == null) return "";
            Encoding enc = Encoding.UTF8;
            return enc.GetString(System.Text.Encoding.UTF8.GetBytes(str));
        }


        private static List<ToDoModel> GetListToDoFromCommand(OracleCommand com)
        {
            List<ToDoModel> items = new List<ToDoModel>();
            ToDoModel item = null;
            string description = string.Empty;
            using (var reader = com.ExecuteReader())
            {
                while (reader.Read())
                {
                    item = new ToDoModel();
                    item.RequestID = (string)reader["RequestID"];
                    item.IDS = (string)reader["IDS"];
                    item.TypeS = (string)reader["TypeS"];
                    item.Submitter = (string)reader["Submitter"];

                    description = reader["Description"] == DBNull.Value ? ((string)reader["BDSummary"]) : ((string)reader["Description"]);
                    //if (description.Length > 15) description = description.Substring(0, 15);

                    item.Description = description;
                    item.Operation = (string)reader["Operation"];
                    item.Status = (string)reader["Status"];
                    item.Priority = reader["Priority"] == DBNull.Value ? "" : (string)reader["Priority"];
                    item.Assigner = reader["Assigner"] == DBNull.Value ? "" : (string)reader["Assigner"];
                    item.AssignGroup = reader["AssignGroup"] == DBNull.Value ? "" : (string)reader["AssignGroup"];
                    item.CreateDate = reader["CreateDate"] == DBNull.Value ? "" : DateTime.Parse(reader["CreateDate"].ToString()).ToString("yyyy/MM/dd HH:mm:ss");
                    items.Add(item);
                }
            }
            com.Dispose();
            return items;
        }

        public static bool IsChangeApprover(string requestid, string uid)
        {
            bool isapprover = false;
            string querystr = "SELECT * FROM aradmin.ap_detail_signature ads INNER JOIN aradmin.chg_infrastructure_change ToDo ON ads.request=ToDo.request_id and ToDo.request_id=:requestid WHERE ads.approval_status=0 AND ads.application='CHG:Infrastructure Change' AND ads.approvers LIKE '%' || :loginID || '%'";
             string connectionStrITSM = ConfigurationManager.ConnectionStrings["ITSMContext"].ConnectionString;
             using (OracleConnection conITSM = new OracleConnection(connectionStrITSM))
             {
                 conITSM.Open();
                 OracleCommand commandITSM;
                 commandITSM = conITSM.CreateCommand();
                 commandITSM.CommandType = System.Data.CommandType.Text;
                 commandITSM.BindByName = true;
                 commandITSM.CommandText = querystr;
                 OracleParameter loginIDITSM = new OracleParameter(":loginID", OracleDbType.NVarchar2, 100, ConvertString(uid), System.Data.ParameterDirection.Input);
                 OracleParameter requestidITSM = new OracleParameter(":requestid", OracleDbType.NVarchar2, 100, ConvertString(requestid), System.Data.ParameterDirection.Input);
                 commandITSM.Parameters.Add(loginIDITSM);
                 commandITSM.Parameters.Add(requestidITSM);
                 OracleDataAdapter adapter = new OracleDataAdapter(commandITSM);
                 DataTable dt = new DataTable();
                 DataSet ds=new DataSet();
                 adapter.Fill(ds);
                 dt = ds.Tables[0];
                 if (dt.Rows.Count > 0)
                 {
                     isapprover = true;
                 }
             }
            return isapprover;
        }
    }
}